IF OBJECT_ID('dbo.csp_rptDepartmentalMetric_SummaryDifference') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.csp_rptDepartmentalMetric_SummaryDifference
END
GO
-- =======================================================================
-- Procedure Name: dbo.csp_rptDepartmentalMetric_SummaryDifference
--
-- Author:      Arthur Bianchini  Partners Research Enterprise Group
--
-- Create date: 8/25/2008
--
-- Description: Gets the difference between the the current and comparison week for the Departmental Metrics Report
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 08/25/09	AAB		TRACKIT 9858
-- =======================================================================

CREATE PROCEDURE dbo.csp_rptDepartmentalMetric_SummaryDifference
(
@PeriodType varchar(50),
@PeriodStartDate smalldatetime,
@PeriodEndDate smalldatetime,
@Institution INT,      
@Department INT = NULL,    
@Unit VARCHAR(250)  = NULL,
@PrincipalInvestigator VARCHAR(50)=NULL,    
@Sponsor  VARCHAR(150) = NULL,    
@FundNumber VARCHAR(50) = NULL, -- no information yet   
@DomainUserId INT =  NULL
)
AS

-- Result Set
CREATE TABLE   #Proposal 
						(agreementID int,
                         Foldernumber varchar(12))

CREATE CLUSTERED INDEX IX_#Proposal_agreementID
    ON #Proposal (AgreementID); 

     INSERT INTO #Proposal
     
     SELECT sec.agreementID,sec.Foldernumber
     FROM INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec 

SELECT
	Institution = COALESCE(x1.Institution, x2.Institution)
,	PendingSubmissionDetails = ISNULL(x1.PendingSubmissionDetails,0) - ISNULL(x2.PendingSubmissionDetails,0)
,	AwardsActivated = ISNULL(x1.AwardsActivated,0) - ISNULL(x2.AwardsActivated,0)
,	AwardsToBeActivated = ISNULL(x1.AwardsToBeActivated,0) - ISNULL(x2.AwardsToBeActivated,0)
,	PCROAwardsToBeActivated = ISNULL(x1.PCROAwardsToBeActivated,0) - ISNULL(x2.PCROAwardsToBeActivated,0)
,	PreAwardJITWeek = ISNULL(x1.PreAwardJITWeek,0) - ISNULL(x2.PreAwardJITWeek,0)
,	AdvanceAccountWeek = ISNULL(x1.AdvanceAccountWeek,0) - ISNULL(x2.AdvanceAccountWeek,0)
,	RenewalsProcessed = ISNULL(x1.RenewalsProcessed,0) - ISNULL(x2.RenewalsProcessed,0)
,	AccountInactivated = ISNULL(x1.AccountInactivated,0) - ISNULL(x2.AccountInactivated,0)
FROM
(
	SELECT
		ai.Institution
	,	x.PeriodType
	,	x.PeriodStartDate
	,	PendingSubmissionDetails = SUM(CASE WHEN Subject='Pending Submission Details' THEN 1 ELSE 0 END)
	,	AwardsActivated = SUM(CASE WHEN Subject='Awards Activated' THEN 1 ELSE 0 END)
	,	AwardsToBeActivated = SUM(CASE WHEN Subject='Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PCROAwardsToBeActivated = SUM(CASE WHEN Subject='PCRO Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PreAwardJITWeek = SUM(CASE WHEN Subject='Pre-Award JIT Week' THEN 1 ELSE 0 END)
	,	AdvanceAccountWeek = SUM(CASE WHEN Subject='Advance Account Week' THEN 1 ELSE 0 END)
	,	RenewalsProcessed = SUM(CASE WHEN Subject='Renewals Processed' THEN 1 ELSE 0 END)
	,	AccountInactivated = SUM(CASE WHEN Subject='Accounts Inactivated' THEN 1 ELSE 0 END)
	FROM
	(
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Accounts Inactivated'
		FROM
			[dbo].[rptMetric_AccountsInactivated]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Advance Account Week'
		FROM
			[dbo].[rptMetric_AdvanceAccountWeek]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards Activated'
		FROM
			[dbo].[rptMetric_AwardActivations]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards To Be Activated'
		FROM
			[dbo].[rptMetric_AwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'PCRO Awards To Be Activated'
		FROM
			[dbo].[rptMetric_PCROAwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pending Submission Details'
		FROM
			[dbo].[rptMetric_PendingSubmissions]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pre-Award JIT Week'
		FROM
			[dbo].[rptMetric_PreAwardJITWeek]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Renewals Processed'
		FROM
			[dbo].[rptMetric_RenewalsProcessed]
		) AS x
INNER JOIN  INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
	ON	x.ProposalNumber = ai.foldernumber
INNER JOIN	#Proposal pr
	ON ai.agreementID = pr.agreementID 
	WHERE
		1=1
	AND	(ai.institutionid= @Institution  OR @Institution IS NULL OR @Institution = 0)
			AND (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 )
			AND (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit='0' OR @Unit='')
			AND	x.PeriodStartDate = @PeriodEndDate
			--AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0')    
			--AND  (ai.Sponsorid = @Sponsor OR @Sponsor IS NULL OR @Sponsor='' OR @Sponsor='0')
			--AND (ai.ProjectFundNumber IN (SELECT * FROM dbo.fnSplitFundNumbers(@FundNumber)) OR @FundNumber IS NULL OR @FundNumber='')
			AND x.PeriodType=@PeriodType 
	GROUP BY
		ai.Institution
	,	x.PeriodType
	,	x.PeriodStartDate
) AS x1
FULL OUTER JOIN
(
	SELECT
		ai.Institution
	,	x.PeriodType
	,	x.PeriodStartDate
	,	PendingSubmissionDetails = SUM(CASE WHEN Subject='Pending Submission Details' THEN 1 ELSE 0 END)
	,	AwardsActivated = SUM(CASE WHEN Subject='Awards Activated' THEN 1 ELSE 0 END)
	,	AwardsToBeActivated = SUM(CASE WHEN Subject='Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PCROAwardsToBeActivated = SUM(CASE WHEN Subject='PCRO Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PreAwardJITWeek = SUM(CASE WHEN Subject='Pre-Award JIT Week' THEN 1 ELSE 0 END)
	,	AdvanceAccountWeek = SUM(CASE WHEN Subject='Advance Account Week' THEN 1 ELSE 0 END)
	,	RenewalsProcessed = SUM(CASE WHEN Subject='Renewals Processed' THEN 1 ELSE 0 END)
	,	AccountInactivated = SUM(CASE WHEN Subject='Accounts Inactivated' THEN 1 ELSE 0 END)
		FROM
	(
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Accounts Inactivated'
		FROM
			[dbo].[rptMetric_AccountsInactivated]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Advance Account Week'
		FROM
			[dbo].[rptMetric_AdvanceAccountWeek]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards Activated'
		FROM
			[dbo].[rptMetric_AwardActivations]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards To Be Activated'
		FROM
			[dbo].[rptMetric_AwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'PCRO Awards To Be Activated'
		FROM
			[dbo].[rptMetric_PCROAwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pending Submission Details'
		FROM
			[dbo].[rptMetric_PendingSubmissions]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pre-Award JIT Week'
		FROM
			[dbo].[rptMetric_PreAwardJITWeek]
		UNION ALL
		SELECT
			Institution
		,	ProposalNumber
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Renewals Processed'
		FROM
			[dbo].[rptMetric_RenewalsProcessed]
		) AS x
INNER JOIN  INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
	ON	x.ProposalNumber = ai.foldernumber
INNER JOIN	#Proposal pr
	ON ai.agreementID = pr.agreementID 
	WHERE
		1=1 
	AND
			(ai.institutionid= @Institution  OR @Institution IS NULL OR @Institution = 0)
			AND (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 )
			AND (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit='0' OR @Unit='')
			AND	x.PeriodStartDate = @PeriodStartDate
			AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0')    
			AND  (ai.Sponsorid = @Sponsor OR @Sponsor IS NULL OR @Sponsor='' OR @Sponsor='0')
			AND (ai.ProjectFundNumber IN (SELECT * FROM dbo.fnSplitFundNumbers(@FundNumber)) OR @FundNumber IS NULL OR @FundNumber='')
			AND x.PeriodType=@PeriodType 
	GROUP BY
		ai.Institution
	,	x.PeriodType
	,	x.PeriodStartDate
) AS x2
	ON x1.Institution = x2.Institution

GO
